package com.hlw.hr_man.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hlw.hr_man.Model.DeptModel;
import com.hlw.hr_man.Model.EmployeeModel;
import com.hlw.hr_man.dao.AddressDao;
import com.hlw.hr_man.dao.EmployeeDao;
import com.hlw.hr_man.util.JDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * @author Eumenides 2016年9月12日下午4:09:13
 */
public class AddressDaoImpl implements AddressDao {

	// 得到总的员工数量
	@Override
	public int getAddressCount() {
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(e_id) scount from hr_employee");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}

	// 得到所有的员工通讯录
	@Override
	public List<EmployeeModel> getAllAddress(int start, int pageSize) {
		// TODO Auto-generated method stub、
		List<EmployeeModel> emps = new ArrayList<EmployeeModel>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select e_number,e_name,e_sex,dept_name,e_tel,e_email,e_address ");
		sql.append(" from hr_employee,hr_department ");
		sql.append("where hr_employee.dept_id = hr_department.dept_id limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				EmployeeModel emp = new EmployeeModel();
				emp.setE_number(rs.getString("e_number"));
				emp.setE_name(rs.getString("e_name"));
				emp.setE_sex(rs.getString("e_sex"));
				emp.setDept_name(rs.getString("dept_name"));
				emp.setE_tel(rs.getString("e_tel"));
				emp.setE_email(rs.getString("e_email"));
				emp.setE_address(rs.getString("e_address"));
				emps.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emps;
	}

	// 通过value查找到的员工的数量
	@Override
	public int getAddressCount(String value) {
		// TODO Auto-generated method stub
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer(
				"select count(e_id) scount from hr_employee where e_number like ? or e_name like ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%" + value + "%");
			pst.setString(2, "%" + value + "%");
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				scount = rs.getInt("scount");
			}
			System.out.println(scount);
			/**
			 * 史上最愚蠢的做法 StringBuffer sql2 = new StringBuffer(
			 * "select count(e_id) scount from hr_employee where e_name like ?"
			 * ); PreparedStatement pst2 =
			 * con.prepareStatement(sql2.toString()); pst2.setString(1, "%" +
			 * value + "%"); ResultSet rs2 = pst2.executeQuery();
			 * while(rs2.next()){ scount += rs2.getInt("scount"); }
			 */
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}

	// 得到查询到的所有员工
	@Override
	public List<EmployeeModel> getSeacherAddress(String value, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<EmployeeModel> emps = new ArrayList<EmployeeModel>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select e_number,e_name,e_sex,dept_name,e_tel,e_email,e_address ");
		sql.append(" from hr_employee,hr_department ");
		sql.append("where hr_employee.dept_id = hr_department.dept_id and e_id in ");
		sql.append("(select e_id from hr_employee where e_number like ? or e_name like ? ) limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%" + value + "%");
			pst.setString(2, "%" + value + "%");
			pst.setInt(3, start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				EmployeeModel emp = new EmployeeModel();
				emp.setE_number(rs.getString("e_number"));
				emp.setE_name(rs.getString("e_name"));
				emp.setE_sex(rs.getString("e_sex"));
				emp.setDept_name(rs.getString("dept_name"));
				emp.setE_tel(rs.getString("e_tel"));
				emp.setE_email(rs.getString("e_email"));
				emp.setE_address(rs.getString("e_address"));
				emps.add(emp);
			}
			/**
			 * 史上最愚蠢的做法 StringBuffer sql2 = new StringBuffer(
			 * "select e_number,e_name,e_sex,dept_name,e_tel,e_email,e_address "
			 * ); sql2.append(" from hr_employee,hr_department "); sql2.append(
			 * "where hr_employee.dept_id = hr_department.dept_id and e_name like ? limit ?,?"
			 * ); PreparedStatement pst2 =
			 * con.prepareStatement(sql2.toString()); pst2.setString(1, "%" +
			 * value + "%"); pst2.setInt(2, start); pst2.setInt(3, pageSize);
			 * ResultSet rs2 = pst2.executeQuery(); while(rs2.next()){
			 * EmployeeModel emp2 = new EmployeeModel();
			 * emp2.setE_number(rs2.getString("e_number"));
			 * emp2.setE_name(rs2.getString("e_name"));
			 * emp2.setE_sex(rs2.getString("e_sex"));
			 * emp2.setDept_name(rs2.getString("dept_name"));
			 * emp2.setE_tel(rs2.getString("e_tel"));
			 * emp2.setE_email(rs2.getString("e_email"));
			 * emp2.setE_address(rs2.getString("e_address")); emps.add(emp2); }
			 */
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emps;
	}

	/**
	 * 得到所有的部门名称
	 */
	@Override
	public JSONArray getAllDept() {
		// TODO Auto-generated method stub
		JSONArray deps = new JSONArray();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select dept_id,dept_name from hr_department");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				JSONObject jarray = new JSONObject();
				jarray.put("id", rs.getInt("dept_id"));
				jarray.put("text", rs.getString("dept_name"));
				deps.add(jarray);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return deps;
	}

	/**
	 * 通过性别找总数
	 */
	@Override
	public int getCountBySex(String sex) {
		// TODO Auto-generated method stub
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(e_id) scount from hr_employee where e_sex = ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, sex);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}

	/**
	 * 通过性别查找
	 */
	@Override
	public List<EmployeeModel> getSexAddress(String sex, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<EmployeeModel> emps = new ArrayList<EmployeeModel>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select e_number,e_name,e_sex,dept_name,e_tel,e_email,e_address ");
		sql.append(" from hr_employee,hr_department ");
		sql.append("where hr_employee.dept_id = hr_department.dept_id and e_sex = ?  limit ?,? ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, sex);
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				EmployeeModel emp = new EmployeeModel();
				emp.setE_number(rs.getString("e_number"));
				System.out.println(emp.getE_name());
				emp.setE_name(rs.getString("e_name"));
				emp.setE_sex(rs.getString("e_sex"));
				emp.setDept_name(rs.getString("dept_name"));
				emp.setE_tel(rs.getString("e_tel"));
				emp.setE_email(rs.getString("e_email"));
				emp.setE_address(rs.getString("e_address"));
				emps.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emps;
	}
	
	//部门id找总数
	@Override
	public int getCountByDept(int deptid) {
		// TODO Auto-generated method stub
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(e_id) scount from hr_employee where dept_id = ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, deptid);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}

	//部门id找员工
	@Override
	public List<EmployeeModel> getAddressByDeptId(int deptid, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<EmployeeModel> emps = new ArrayList<EmployeeModel>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select e_number,e_name,e_sex,dept_name,e_tel,e_email,e_address ");
		sql.append(" from hr_employee,hr_department ");
		sql.append("where hr_employee.dept_id = hr_department.dept_id and hr_department.dept_id = ?  limit ?,? ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, deptid);
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				EmployeeModel emp = new EmployeeModel();
				emp.setE_number(rs.getString("e_number"));
				System.out.println(emp.getE_name());
				emp.setE_name(rs.getString("e_name"));
				emp.setE_sex(rs.getString("e_sex"));
				emp.setDept_name(rs.getString("dept_name"));
				emp.setE_tel(rs.getString("e_tel"));
				emp.setE_email(rs.getString("e_email"));
				emp.setE_address(rs.getString("e_address"));
				emps.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emps;
	}

}
